﻿using System;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using OAuthApp.Data;
using Microsoft.AspNetCore.Mvc;
using Swashbuckle.AspNetCore.Annotations;
using OAuthApp.ApiModels.AppRankController;
using OAuthApp.Services;
using Microsoft.Identity.Client.Extensions.Msal;

namespace OAuthApp.Apis
{
    [SwaggerTag("应用排行榜")]
    [ServiceFilter(typeof(ApiRequestLoggingAttribute))]
    public class AppRankController : BaseController
    {
        #region services
        private readonly AppDbContext _context;
        #endregion

        #region construct
        public AppRankController(AppDbContext context)
        {
            _context = context;
        }
        #endregion

        string AppRankTableName
        {
            get
            {
                return "_AppRank_" + Request.RouteValues["appId"].ToString();
            }
        }

        string RankKey
        {
            get
            {
                return Request.RouteValues["rankKey"].ToString();
            }
        }

        #region 激活排行榜
        /// <summary>
        /// 激活排行榜
        /// </summary>
        /// <param name="appID"></param>
        /// <returns></returns>
        [HttpPost("{appId}/Active")]
        [SwaggerOperation(OperationId = "AppRankActive")]
        public IActionResult Active()
        {
            var tName = _context.QueryFirstOrDefault<string>(
                "select name from sqlite_master WHERE TYPE = 'table' AND tbl_name = '" + AppRankTableName + "'");

            if (string.IsNullOrWhiteSpace(tName))
            {
                _context.Execute(@"CREATE TABLE " + AppRankTableName + " (" +
                "ID           INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT," +
                "Avatar       TEXT," +
                "CreateDate   DATETIME NOT NULL," +
                "Data         TEXT," +
                "IsDelete     BOOLEAN  NOT NULL," +
                "LastUpdate   DATETIME NOT NULL," +
                "MaximumScore INTEGER  NOT NULL," +
                "NickName     TEXT," +
                "Platform     TEXT," +
                "RankKey      TEXT," +
                "Score        INTEGER  NOT NULL," +
                "UnionID      TEXT," +
                "UserID       INTEGER  NOT NULL" +
                ");");
            }

            return Ok(true);
        }
        #endregion

        #region 是否激活排行榜
        /// <summary>
        /// 是否激活排行榜
        /// </summary>
        /// <param name="appID"></param>
        /// <returns></returns>
        [HttpGet("{appId}/IsActived")]
        [SwaggerOperation(OperationId = "AppRankIsActived")]
        public IActionResult IsActived()
        {
            var tName = _context.QueryFirstOrDefault<string>(
                "select name from sqlite_master WHERE TYPE = 'table' AND tbl_name = '" + AppRankTableName + "'");

            var result = string.IsNullOrWhiteSpace(tName) ? false : true;

            return Ok(result);
        } 
        #endregion

        #region 统计
        /// <summary>
        /// 统计
        /// </summary>
        /// <returns></returns>
        [HttpGet("{appId}/Report")]
        [SwaggerOperation(OperationId = "AppRankReport")]
        public IActionResult Report()
        {
            var sql = "SELECT RankKey as ID ,COUNT(1) AS Count," +
                    " SUM(SCORE) as TotalScore,MAX(MaximumScore) AS TopScore," +
                    " MIN(CreateDate) as CreateDate FROM " + AppRankTableName + " GROUP BY RankKey";

            var result = _context.Query<GetResponse>(sql);

            return OK(result);
        }
        #endregion

        #region 列表
        /// <summary>
        /// 列表
        /// </summary>
        /// <param name="pltform">用户来源</param>
        /// <param name="take">获取条数</param>
        /// <param name="skip">跳过条数</param>
        /// /// <returns></returns>
        [HttpGet("{appId}/{rankKey}")]
        [SwaggerOperation(OperationId = "AppRankList")]
        public IActionResult List([FromQuery] string pltform, [FromQuery] int take = 30, [FromQuery] int skip = 0)
        {
            var sql = "SELECT * FROM " + AppRankTableName;

            var sql2 = "SELECT COUNT(1) FROM " + AppRankTableName;
            
            var where = " WHERE RankKey = @rankKey";

            if (!string.IsNullOrWhiteSpace(pltform))
            {
                where += " AND Platform = @pltform";
            }

            var cmd = sql + where + " ORDER BY MaximumScore DESC" +
                 $" LIMIT {take} OFFSET {skip}";

            var sqlParams = new
            {
                rankKey = RankKey,
                pltform = pltform
            };

            var total = _context.QueryFirstOrDefault<long>(sql2 + where, sqlParams);

            var data = _context.Query<AppRank>(cmd, sqlParams);

            return OK(new
            {
                total,
                data,
                take,
                skip,
            });
        }
        #endregion

        #region 提交或更新分数
        /// <summary>
        /// 提交或更新分数
        /// </summary>
        /// <param name="value">用户数据</param>
        /// <returns></returns>
        [HttpPut("{appId}/{rankKey}")]
        [SwaggerOperation(OperationId = "AppRankPut")]
        public IActionResult Put([Required][FromBody]PutRequest value)
        {
            var cmd = "SELECT * FROM " + AppRankTableName;
            
            var where = " WHERE RankKey= @RankKey AND Platform = @Platform AND UnionID = @UnionID";

            var entity = _context.QueryFirstOrDefault<AppRank>(cmd + where, new
            {
                RankKey = RankKey,
                Platform = value.Platform,
                UnionID = value.UnionID
            });


            if (entity != null)
            {
                if (!string.IsNullOrWhiteSpace(value.Avatar))
                {
                    entity.Avatar = value.Avatar;
                }

                if (!string.IsNullOrWhiteSpace(value.NickName))
                {
                    entity.NickName = value.NickName;
                }

                if (!string.IsNullOrWhiteSpace(value.Data))
                {
                    entity.Data = value.Data;
                }

                if (entity.MaximumScore < value.Score)
                {
                    entity.MaximumScore = value.Score;
                }

                entity.Score = value.Score;

                entity.LastUpdate = DateTime.UtcNow;
            }

            else
            {
                entity = new AppRank()
                {
                    Avatar = value.Avatar,
                    Data = value.Data,
                    UserID = UserID,
                    NickName = value.NickName,
                    UnionID = value.UnionID,
                    Platform = value.Platform,
                    RankKey = RankKey,
                    Score = value.Score,
                    MaximumScore = value.Score
                };

                _context.AppRanks.Add(entity);
            }

            try
            {
                _context.SaveChanges();
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }

            return OK(true);
        }
        #endregion

        #region 我的排名
        string MyRank_CMD_1
        {
            get
            {
                return "SELECT * FROM " +
            "(SELECT ROW_NUMBER() OVER(ORDER BY Score DESC) AS RankIndex," +
                "Avatar,UnionID,NickName,Score,Data,[Platform],MaximumScore " +
                "FROM " + AppRankTableName + " WHERE RankKey=@RankKey) A" +
            "  WHERE A.[Platform]=@Platform AND A.UnionID=@UnionID";
            }
        }

        string MyRank_CMD_2
        {
            get
            {
                return "SELECT A.RankIndex FROM " +
           " (SELECT ROW_NUMBER() OVER(ORDER BY MaximumScore DESC) AS RankIndex, " +
           " UnionID,[Platform] " +
           " FROM " + AppRankTableName + " WHERE RankKey=@RankKey) A " +
           " WHERE A.[Platform]=@Platform AND A.UnionID=@UnionID LIMIT 1";
            }
        } 

        string MyRank_CMD_3
        {
            get
            {
                return "SELECT COUNT(1) FROM " + AppRankTableName + " WHERE AppID = @AppID AND RankKey=@RankKey";
            }
        }

        /// <summary>
        /// 我的排名
        /// </summary>
        /// <param name="platform">用户来源</param>
        /// <param name="unionID">UnionID</param>
        /// /// <returns></returns>
        [HttpGet("{appId}/{rankKey}/Me")]
        [SwaggerOperation(OperationId = "AppRankMe")]
        public IActionResult Me([Required][FromQuery] string platform,
            [Required][FromQuery] string unionID)
        {
            var result = _context.QueryFirstOrDefault<MeReponse>(MyRank_CMD_1,
                new
                {
                    AppID,
                    RankKey,
                    Platform = platform,
                    UnionID = unionID
                });

            if(result==null)
            {
                return Error("数据不存在");
            }

            var _BetterRankIndex = _context.QueryFirstOrDefault<string>(MyRank_CMD_2,
                new
                {
                    AppID,
                    RankKey,
                    Platform = platform,
                    UnionID = unionID
                });

            if (!string.IsNullOrWhiteSpace(_BetterRankIndex))
            {
                result.BetterRankIndex = long.Parse(_BetterRankIndex);
            }

            var _RankTotalCount = _context.QueryFirstOrDefault<string>(MyRank_CMD_3,
                new
                {
                    AppID,
                    RankKey
                });

            if (!string.IsNullOrWhiteSpace(_RankTotalCount))
            {
                result.RankTotalCount = long.Parse(_RankTotalCount);
            }

            if (result.Score == 0)
            {
                result.BeyoundPercent = 0;
            }

            else if (result.RankIndex == 1)
            {
                result.BeyoundPercent = 100;
            }

            else
            {
                result.BeyoundPercent = (decimal)Math.Round(100.00 - result.RankIndex / (double)result.RankTotalCount * 100.00, 2);
            }

            return OK(result);
        }
        #endregion
    }
}
